package unit;

import com.google.gson.Gson;
import model.*;

import java.io.IOException;
import java.sql.SQLException;

public final class ToLocalClass {
    private RemoteInterFaceClass remoteInterFaceClass;
    private SqliteClass sqliteClass;
    private String sn;
    private String mac;
    private Gson gson;

    public ToLocalClass(RemoteInterFaceClass remoteInterFaceClass, SqliteClass sqliteClass, String sn, String mac) {
        this.remoteInterFaceClass = remoteInterFaceClass;
        this.sqliteClass = sqliteClass;
        this.sn = sn;
        this.mac = mac;
        this.gson = new Gson();
    }

    /**
     * @return accessToken及一套令牌
     * @throws IOException
     */
    private String getAccessToken() throws IOException {
        String akfPara = MyTokenClass.getAkfPara();
        if (akfPara != null) {
            return akfPara;
        } else {
            String methodUrl = String.format("loginEquipment/faceLogController/EquipmentLogBySnAndMac?sn=%s&mac=%s"
                    , sn
                    , mac);
            final String log = remoteInterFaceClass.getLog(methodUrl);
            final ResultVO resultVO = gson.fromJson(log, ResultVO.class);
            if (resultVO.getCode() == 0) {
                final LogResultModel logResultModel = gson.fromJson(resultVO.toString(), LogResultModel.class);
                if (logResultModel != null) {
                    MyTokenClass.setSystemId(logResultModel.getSystemId());
                    MyTokenClass.setUseId(logResultModel.getUseId());
                    MyTokenClass.setAccessToken(logResultModel.getAccessToken());
                    MyTokenClass.setClientType(logResultModel.getClientType());
                    MyTokenClass.setUseType(logResultModel.getUseType());
                    MyTokenClass.setUseClass(logResultModel.getUseClass());
                    MyTokenClass.setDays(logResultModel.getDays());
                    return MyTokenClass.getAkfPara();
                } else
                    return null;
            } else
                return null;
        }
    }

    private String getMethodUrl(String methodUrl) throws IOException {
        String returnUrl = methodUrl;
        final String accessToken = getAccessToken();
        if (accessToken != null) {
            returnUrl += "&" + accessToken;
        }
        return returnUrl;
    }

    public String getDoorMessageList() throws IOException {
        String methodUrl = "door/equipmentMessageFeignController/selectEquipmentMessageBySn?";
        return remoteInterFaceClass.getDoorParamList(getMethodUrl(methodUrl));
    }
    public String getDoorAdvList() throws IOException {
        String methodUrl = "door/equipmentAdvertisingFeignController/selectEquipmentAdvertising?";
        return remoteInterFaceClass.getDoorParamList(getMethodUrl(methodUrl));
    }

    public String getDoorParamList() throws IOException {
        //卫立星，请注意，limit = 0，则，不分页，否则，按limit值进行分页；
        String methodUrl = "door/equipParaController/selectEquipParamsBySn?limit=0";
        return remoteInterFaceClass.getDoorParamList(getMethodUrl(methodUrl));
    }

    public String getDoorCardList(int page, int limit) throws IOException {
        String methodUrl = "door/doorAuthorityFeignController/getDoorCardAuthorityTest?" +
                "limit=" + limit + "&"
                + "page=" + page;
        return remoteInterFaceClass.getDoorParamList(getMethodUrl(methodUrl));
    }

    public String getDoorFaceList(int page, int limit) throws IOException {
        String methodUrl = "door/doorAuthorityFeignController/getDoorPictureAuthorityTest?" +
                "limit=" + limit + "&"
                + "page=" + page;
        return remoteInterFaceClass.getDoorParamList(getMethodUrl(methodUrl));
    }

    public int DeleteDoorMessage(boolean newSign) throws SQLException {
        if (newSign)
            return sqliteClass.clearTable("vou_EquipmentMessage_new");
        else
            return sqliteClass.clearTable("vou_EquipmentMessage");
    }
    public int DeleteDoorAdv(boolean newSign) throws SQLException {
        if (newSign)
            return sqliteClass.clearTable("vou_EquipmentAdvertising_new");
        else
            return sqliteClass.clearTable("vou_EquipmentAdvertising");
    }

    public int DeleteDoorParam(boolean newSign) throws SQLException {
        if (newSign)
            return sqliteClass.clearTable("inf_EquipmentParam_new");
        else
            return sqliteClass.clearTable("inf_EquipmentParam");
    }

    public int DeleteDoorCardParam(boolean newSign) throws SQLException {
        if (newSign)
            return sqliteClass.clearTable("vou_cardAuthority_new");
        else
            return sqliteClass.clearTable("vou_cardAuthority");
    }

    public int DeleteDoorFaceParam(boolean newSign) throws SQLException {
        if (newSign)
            return sqliteClass.clearTable("vou_faceAuthority_new");
        else
            return sqliteClass.clearTable("vou_faceAuthority");
    }

    public int insertDoorMessage(EquipmentMessageModel equipmentMessageModel) throws SQLException {
        String sql = String.format("insert into vou_EquipmentMessage_new(id,message,messageType" +
                        ",createName,createTime" +
                        ",sortNo,beginDate,endDate" +
                        ",colorR,colorG,colorB,fontSize)" +
                        " values('%s','%s',%s" +
                        ",'%s','%s'" +
                        ",%s,'%s','%s'" +
                        ",%s,%s,%s,%s)"
                , equipmentMessageModel.getId()
                , equipmentMessageModel.getMessage()
                , equipmentMessageModel.getMessageType()
                , equipmentMessageModel.getCreateName()
                , equipmentMessageModel.getCreateTime()
                , equipmentMessageModel.getSortNo()
                , equipmentMessageModel.getBeginDate()
                , equipmentMessageModel.getEndDate()
                , equipmentMessageModel.getColorR()
                , equipmentMessageModel.getColorG()
                , equipmentMessageModel.getColorB()
                , equipmentMessageModel.getFontSize()
        );
        return sqliteClass.insertSql(sql);
    }
    public int insertDoorAdv(EquipmentAdvertisingModel equipmentAdvertisingModel) throws SQLException {
        String sql = String.format("insert into vou_EquipmentAdvertising_new(id,sortNo,beginDate,endDate)" +
                        " values('%s',%s,'%s','%s')"
                , equipmentAdvertisingModel.getId()
                , equipmentAdvertisingModel.getSortNo()
                , equipmentAdvertisingModel.getBeginDate()
                , equipmentAdvertisingModel.getEndDate()
        );
        return sqliteClass.insertSql(sql);
    }

    public int insertDoorPara(EquipParaModel equipParaModel) throws SQLException {
        String sql = String.format("insert into inf_EquipmentParam_new" +
                        " values('%s','%s','%s')"
                , equipParaModel.getId()
                , equipParaModel.getParam()
                , equipParaModel.getRemark());
        return sqliteClass.insertSql(sql);
    }

    public int insertDoorFacePara(DoorPictureModel doorPictureModel) throws SQLException {
        String sql = String.format("insert into vou_faceAuthority_new(perId" +
                        ",personType" +
                        ",picId" +
                        ",validTime" +
                        ",crcCode)" +
                        " values('%s','%s','%s','%s','%s')"
                , doorPictureModel.getPerId()
                , doorPictureModel.getPersonType()
                , doorPictureModel.getPicId()
                , doorPictureModel.getValidTime()
                , doorPictureModel.getCrcCode()
        );
        return sqliteClass.insertSql(sql);
    }

    public int insertDoorCardPara(DoorCardModel doorCardModel) throws SQLException {
        String sql = String.format("insert into vou_cardAuthority_new(perId" +
                        ",personType" +
                        ",cardId" +
                        ",validTime" +
                        ",crcCode)" +
                        " values('%s','%s','%s','%s','%s')"
                , doorCardModel.getPerId()
                , doorCardModel.getPersonType()
                , doorCardModel.getCardId()
                , doorCardModel.getValidTime()
                , doorCardModel.getCrcCode()
        );
        return sqliteClass.insertSql(sql);
    }

    public int insertDoorFaceOldPara() throws SQLException {
        int count = 0;
        String sql = "DELETE \n" +
                "FROM vou_faceAuthority\n" +
                "WHERE picId not in(\n" +
                "SELECT picId\n" +
                "FROM vou_faceAuthority_new)";
        count += sqliteClass.insertSql(sql);
        sql = "INSERT INTO vou_faceAuthority (perId,personType,picId,validTime,crcCode,updateSign)\n" +
                "SELECT perId,personType,picId,validTime,crcCode,updateSign\n" +
                "FROM vou_faceAuthority_new\n" +
                "WHERE picId not in(\n" +
                "SELECT picId\n" +
                "FROM vou_faceAuthority)";
        count += sqliteClass.insertSql(sql);
        sql = "UPDATE vou_faceAuthority\n" +
                "\tSET personType = case WHEN (SELECT personType FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode != vou_faceAuthority_new.crcCode) is null\n" +
                "then personType else (SELECT personType FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode != vou_faceAuthority_new.crcCode) end\n" +
                "\t, perId = case WHEN (SELECT perId FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode != vou_faceAuthority_new.crcCode) is null\n" +
                "then perId else (SELECT perId FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode != vou_faceAuthority_new.crcCode) end\n" +
                "\t, validTime = case WHEN (SELECT validTime FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode <> vou_faceAuthority_new.crcCode) is null\n" +
                "then validTime else (SELECT validTime FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode <> vou_faceAuthority_new.crcCode)  end\n" +
                "\t, crcCode = case WHEN (SELECT crcCode FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode <> vou_faceAuthority_new.crcCode) is null\n" +
                "then crcCode else (SELECT crcCode FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode <> vou_faceAuthority_new.crcCode)  end\n" +
                "\t, updateSign = case WHEN (SELECT updateSign FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode <> vou_faceAuthority_new.crcCode) is null\n" +
                "then updateSign else (SELECT updateSign FROM vou_faceAuthority_new WHERE vou_faceAuthority.picId = vou_faceAuthority_new.picId AND vou_faceAuthority.crcCode <> vou_faceAuthority_new.crcCode)  end";
        count += sqliteClass.insertSql(sql);
        return count;
    }

    public int insertDoorCardOldPara() throws SQLException {
        int count = 0;
        String sql = "DELETE FROM vou_cardAuthority" +
                " WHERE perId not in(" +
                " SELECT perId" +
                " FROM vou_cardAuthority_new)";
        count += sqliteClass.insertSql(sql);
        sql = "INSERT INTO vou_cardAuthority (perId,personType,cardId,validTime,crcCode)" +
                " SELECT perId,personType,cardId,validTime,crcCode" +
                " FROM vou_cardAuthority_new" +
                " WHERE perId not in(" +
                " SELECT perId" +
                " FROM vou_cardAuthority)";
        count += sqliteClass.insertSql(sql);
        sql = "UPDATE vou_cardAuthority\n" +
                "SET personType = case WHEN (SELECT personType FROM vou_cardAuthority_new WHERE vou_cardAuthority.perId = vou_cardAuthority_new.perId AND vou_cardAuthority.crcCode != vou_cardAuthority_new.crcCode) is null then personType else (SELECT personType FROM vou_cardAuthority_new WHERE vou_cardAuthority.perId = vou_cardAuthority_new.perId AND vou_cardAuthority.crcCode != vou_cardAuthority_new.crcCode) end\n" +
                ", cardId = case WHEN (SELECT cardId FROM vou_cardAuthority_new WHERE vou_cardAuthority.perId = vou_cardAuthority_new.perId AND vou_cardAuthority.crcCode != vou_cardAuthority_new.crcCode) is null\n" +
                "then cardId else (SELECT cardId FROM vou_cardAuthority_new WHERE vou_cardAuthority.perId = vou_cardAuthority_new.perId AND vou_cardAuthority.crcCode != vou_cardAuthority_new.crcCode) end\n" +
                ", validTime = case WHEN (SELECT validTime FROM vou_cardAuthority_new WHERE vou_cardAuthority.perId = vou_cardAuthority_new.perId AND vou_cardAuthority.crcCode <> vou_cardAuthority_new.crcCode) is null\n" +
                "then validTime else (SELECT validTime FROM vou_cardAuthority_new WHERE vou_cardAuthority.perId = vou_cardAuthority_new.perId AND vou_cardAuthority.crcCode <> vou_cardAuthority_new.crcCode)  end\n" +
                ", crcCode = case WHEN (SELECT crcCode FROM vou_cardAuthority_new WHERE vou_cardAuthority.perId = vou_cardAuthority_new.perId AND vou_cardAuthority.crcCode <> vou_cardAuthority_new.crcCode) is null\n" +
                "then crcCode else (SELECT crcCode FROM vou_cardAuthority_new WHERE vou_cardAuthority.perId = vou_cardAuthority_new.perId AND vou_cardAuthority.crcCode <> vou_cardAuthority_new.crcCode)  end\n";
        count += sqliteClass.insertSql(sql);
        return count;
    }

    public int insertDoorOldPara() throws SQLException {
        this.DeleteDoorParam(false);//清空工作表的记录
        String sql = "insert into inf_EquipmentParam Select id,param,remark From inf_EquipmentParam_new";
        return sqliteClass.insertSql(sql);
    }

    public int insertDoorOldAdv() throws SQLException {
        this.DeleteDoorAdv(false);//清空工作表的记录
        String sql = "insert into vou_EquipmentAdvertising Select id,sortNo,beginDate,endDate" +
                " From vou_EquipmentAdvertising_new";
        return sqliteClass.insertSql(sql);
    }
    public int insertDoorOldMessage() throws SQLException {
        this.DeleteDoorMessage(false);//清空工作表的记录
        String sql = "insert into vou_EquipmentMessage Select id," +
                "message," +
                "messageType," +
                "createName," +
                "createTime," +
                "sortNo," +
                "beginDate," +
                "endDate, " +
                "colorR, " +
                "colorG, " +
                "colorB, " +
                "fontSize " +
                "From vou_EquipmentMessage_new";
        return sqliteClass.insertSql(sql);
    }
}
